const { execSQL } = require("../db/mysql");
const { nightnumSQL2, occupancySQL2 } = require("../db/sqlStatement");

function hotel_list_show(hotellist) {//酒店列表展示，标记是否参于租赁,hotelId,hotelName,roomnum,isReant
    var date = new Date();
    date = date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate();
    var sql = `SELECT h.hotelId,h.name,roomnum,IFNULL(hr.status,0) isRent FROM (
        SELECT hotelId,hotel.name,COUNT(*) roomnum FROM hotel INNER JOIN hotel_room hr ON hotel.id=hr.hotelId`;
    if (hotellist) sql += ` WHERE hotelId IN (${hotellist})`
    sql += ` GROUP BY hotel.id
        ) h LEFT OUTER JOIN hotel_rent hr ON h.hotelId=hr.hotelId AND hr.gmtCreate<'${date}' AND hr.rentDeadline>'${date}'`;// AND hr.gmtCreate< AND hr.rentDeadline<
    // console.log(sql)
    return execSQL(sql).then(data => {
        var arrs = {};
        for (var key in data) {
            arrs[data[key].hotelId] = {
                'name': data[key].name, 'roomnum': data[key].roomnum, 'isRent': data[key].isRent
            }
        }
        return arrs;
    })
}
// var hotel_list_show=hotel_list_show('2,3,4');
function rent_hotel_data2(hotellist) {//酒店数据
    return hotel_list_show(hotellist).then(data => {
        // console.log(data)
        var rentHotelNum = 0, hotelRoomNum = 0;
        for (var key in data) {
            if (data[key].isRent) rentHotelNum++;
            hotelRoomNum += data[key].roomnum;
        }
        return {
            'hotelNum': Object.getOwnPropertyNames(data).length,
            'rentHotelNum': rentHotelNum,
            'hotelRoomNum': hotelRoomNum
        };
    })
}
// rent_hotel_data2('2,3,4').then(data=>console.log(data))
async function option_occupancyAll(hotellist) {//总入住率
    var date = ['hr.gmtCreate', 'hr.rentdeadline'];//总夜间数
    // var sql=`SELECT hds.hotelId,COUNT(*) night FROM hotel_daily_settlement hds INNER JOIN
    // hotel_rent hr ON hds.hotelId=hr.hotelId AND ${date[0]}<hds.from AND ${date[1]}>hds.from WHERE hds.hotelId IN (${arr.toString()}) GROUP BY hotelId`
    var sql1 = nightnumSQL2(date, hotellist);
    var nightnumAll = await execSQL(sql1);
    var sql2 = occupancySQL2(date, hotellist);
    var occupancyAll = await execSQL(sql2).then(data => {
        var occupancy = 0;
        for (var i = 0; i < data.length; i++) {
            occupancy += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
        }
        return occupancy;
    });
    var arr = [], index = 24;
    for (var i = 0; i < 24; i++) {
        date = weeks7(date[0]);
        sql1 = nightnumSQL2(date, hotellist);
        var weeknightnum = await execSQL(sql1);
        sql2 = occupancySQL2(date, hotellist);
        if (i != 23) {
            execSQL(sql2).then(data => {
                var occupancy = 0;
                for (var i = 0; i < data.length; i++) {
                    occupancy += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
                }
                arr.push({ 'week': index--, 'livedRate': (occupancy / weeknightnum[0].num) || 0 });
            });
        } else {
            return execSQL(sql2).then(data => {
                var occupancy = 0;
                for (var i = 0; i < data.length; i++) {
                    occupancy += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
                }
                arr.push({ 'week': 1, 'livedRate': (occupancy / weeknightnum[0].num) || 0 });
                return { 'totalRoomNumber': nightnumAll[0].num, 'totalLivedRoomNumber': occupancyAll, 'hotelnumChange': arr };
            });
        }
    }
}
// rental_dealing('2,3').then(data=>console.log(data))//总租金收取情况,在任务2中
function option_occupancy(hotelList, left, right) {//选中酒店入住率
    var sql = `SELECT hds.hotelId,hds.from,hds.desc,hds.isSettled FROM hotel_daily_settlement hds WHERE hotelId in (${hotelList}) and hds.from>'${left}' AND hds.from<'${right}' ORDER BY hotelId`;
    return Promise.all([execSQL(sql), hotel_list_show(hotelList)]).then(([data, hotelData]) => {
        var arrs = {};
        var timePeriod = timesPeriod(left, right);//判断时间单位shi月、周、日
        for (var i = 0; i < data.length; i++) {
            if (timePeriod == '月') var fromtime = data[i].from.getFullYear() + '-' + (data[i].from.getMonth() + 1);
            else if (timePeriod == '周') var fromtime = sundayFromdate(data[i].from);//将日期转换成本周周日或一
            else var fromtime = data[i].from.getFullYear() + '-' + (data[i].from.getMonth() + 1) + '-' + data[i].from.getDate();
            if (arrs[fromtime]) {
                arrs[fromtime].lived += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
                arrs[fromtime].roomnum += hotelData[data[i].hotelId].roomnum;
            } else arrs[fromtime] = {
                'from': fromtime,
                'lived': parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）"))),
                'roomnum': hotelData[data[i].hotelId].roomnum
                // 'rentalAll':parseInt(data[i].desc.substring(data[i].desc.indexOf("共计：")+3,data[i].desc.indexOf("元。"))),
                // //'prepaidRental':(data[i].isSettled==1)?parseInt(data[i].desc.substring(data[i].desc.indexOf("共计：")+3,data[i].desc.indexOf("元。"))):0
            };
        }
        return arrs;
    }).then(data => {
        var arrs = [];
        for (var key in data) arrs.push(data[key]);
        return arrs;
    })
}
// option_occupancy([2,3],'2022-2-10','2022-8-5').then(data=>console.log(data));
function option_rental(hotelList, leftTime, rightTime) {//选中酒店租金部分，租金收取情况hotel_rent_order
    var sql = `SELECT hotelId,hotelName,gmtCreate,payAmountCent,payTime,hro.desc,amountCent FROM hotel_rent_order hro WHERE hotelId in (${hotelList}) and gmtCreate>'${leftTime}' AND gmtCreate<'${rightTime}'`;
    return Promise.all([execSQL(sql), timesPeriod(leftTime, rightTime)]).then(([data, timePeriod]) => {//判断时间区间为月、周、日
        var arrs = {};
        // console.log(timePeriod)
        for (var i = 0; i < data.length; i++) {
            if (timePeriod == '月') var createTime = data[i].gmtCreate.getFullYear() + '-' + (data[i].gmtCreate.getMonth() + 1);
            // else if(timePeriod=='周') var createTime=sundayFromdate(data[i].gmtCreate);//将日期转换成本周周一
            else var createTime = data[i].gmtCreate.getFullYear() + '-' + (data[i].gmtCreate.getMonth() + 1) + '-' + data[i].gmtCreate.getDate();
            if (arrs[createTime]) {
                arrs[createTime].rental += data[i].amountCent / 100;
                arrs[createTime].payAmountCent += data[i].payAmountCent / 100;
            } else arrs[createTime] = {
                // 'hotelId':data[i].hotelId,'hotelName':data[i].hotelName,
                'rental': data[i].amountCent / 100,
                'gmtCreate': createTime,
                'payAmountCent': data[i].payAmountCent / 100,
                // 'payTime':(data[i].payTime)?data[i].payTime.getFullYear()+'-'+(data[i].payTime.getMonth()+1)+'-'+data[i].payTime.getDate():null
            };
        }
        return arrs;
    }).then(data => {
        var arrs = [];
        for (var key in data) arrs.push(data[key])
        return arrs;
    });;
}
// option_rental('2,3','2022-2-1','2022-8-8').then(data=>console.log(data))
function hotel_data_proportion(hotellist, left, right) {//房间数、入住率、应收租金
    var sql2 = occupancySQL2(['\'' + left + '\'', '\'' + right + '\''], hotellist);
    // console.log(sql1)
    return Promise.all([hotel_list_show(hotellist), execSQL(sql2)]).then(([data2, data]) => {//hotelId,night,roomnum//hotelId,desc
        var arrs = {};//hotelId,hotelName,roomnum,isReant
        for (var i in data2) {
            arrs[i] = { 'hotelName': data2[i].name, 'roomnum': data2[i].roomnum }
        }
        for (var i = 0; i < data.length; i++) {
            var lived = parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
            var receivable = data[i].amount;
            arrs[data[i].hotelId]['lived'] = (arrs[data[i].hotelId]['lived']) ? arrs[data[i].hotelId].lived + lived : lived;
            arrs[data[i].hotelId]['receivable'] = (arrs[data[i].hotelId]['receivable']) ? arrs[data[i].hotelId].receivable + receivable : receivable;
            arrs[data[i].hotelId]['nightnum'] = (arrs[data[i].hotelId]['nightnum']) ? arrs[data[i].hotelId]['nightnum'] + arrs[data[i].hotelId].roomnum : arrs[data[i].hotelId].roomnum;
        }
        return arrs;
    }).then(data => {
        var arrs = [];
        for (var key in data) {
            arrs.push({ 'property': '房间数', 'hotelName': data[key].hotelName, 'value': data[key].roomnum });
            arrs.push({ 'property': '入住率', 'hotelName': data[key].hotelName, 'value': data[key].lived / data[key].nightnum });
            arrs.push({ 'property': '租金', 'hotelName': data[key].hotelName, 'value': data[key].receivable });
        }
        return arrs;
    });
}
// hotel_data_proportion('2,3','2022-6-1','2022-8-1').then(data=>console.log(data))
function weeks7(right) {//获取本周周日日期或上周周日日期
    if (right.indexOf('23:59:59') < 0) {
        var date = new Date();
        switch (date.getDay()) {
            case 1: date = new Date(date.setDate(date.getDate() + 6));
                break;
            case 2: date = new Date(date.setDate(date.getDate() + 5));
                break;
            case 3: date = new Date(date.setDate(date.getDate() + 4));
                break;
            case 4: date = new Date(date.setDate(date.getDate() + 3));
                break;
            case 5: date = new Date(date.setDate(date.getDate() + 2));
                break;
            case 6: date = new Date(date.setDate(date.getDate() + 1));
                break;
        }
        right = date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate() + ' 23:59:59';
    } else right = right.substring(1, right.length - 1);
    var left = new Date(right);
    left = new Date(left.setDate(left.getDate() - 7));
    left = '\'' + left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate() + ' 23:59:59' + '\'';
    right = '\'' + right + '\'';
    return [left, right];
}
function timesPeriod(left, right) {//判断时间范围区间
    var dayNum = (new Date(right) - new Date(left)) / 86400000;
    if (dayNum > 168) return '月';//在同年同月，getFullyear,getMounte
    else if (dayNum >= 30) return '周';
    else return '日';
}
// console.log(timesPeriod('2022-7-21','2022-8-1'));
function getYearWeek(a, b, c) {//输入日期，判断是今年第几周；无须这样做了，直接将日期转换成本周周一
    var date1 = new Date(a, parseInt(b) - 1, c),
        date2 = new Date(a, 0, 1),//今年第一天
        d = Math.round((date1.valueOf() - date2.valueOf()) / 86400000);
    return Math.ceil((d + (date2.getDay() + 1 - 1)) / 7);
}
function sundayFromdate(date) {//将日期转化为本周星期日
    date = new Date(date);
    switch (date.getDay()) {
        case 1: date = new Date(date.setDate(date.getDate() + 6));
            break;
        case 2: date = new Date(date.setDate(date.getDate() + 5));
            break;
        case 3: date = new Date(date.setDate(date.getDate() + 4));
            break;
        case 4: date = new Date(date.setDate(date.getDate() + 3));
            break;
        case 5: date = new Date(date.setDate(date.getDate() + 2));
            break;
        case 6: date = new Date(date.setDate(date.getDate() + 1));
            break;
    }
    return date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate();
}
module.exports = {
    hotel_list_show,
    rent_hotel_data2,
    option_occupancyAll,
    option_occupancy,
    option_rental,
    hotel_data_proportion,
    timesPeriod, sundayFromdate
}